/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/*
 * SoulToNGLTool.java
 *
 * Created on 14 Jun, 2011, 12:05:15 PM
 */
package org.verus.ngl.migration;

/**
 *
 * @author yogesh
 */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Hashtable;
import javax.swing.JOptionPane;
import newgenlib.marccomponent.marcmodel.CatalogMaterialDescription;
import newgenlib.marccomponent.marcmodel.Field;
import newgenlib.marccomponent.marcmodel.SubField;
import newgenlib.marccomponent.persistence.PersistMARCRecord;

public class SoulToNGLTool extends javax.swing.JFrame {

    private java.sql.Connection con = null;

    public SoulToNGLTool() {
        initComponents();
    }

    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {
        java.awt.GridBagConstraints gridBagConstraints;

        jPanel4 = new javax.swing.JPanel();
        jPanel2 = new javax.swing.JPanel();
        jLabel11 = new javax.swing.JLabel();
        tfHost1 = new javax.swing.JTextField();
        jLabel12 = new javax.swing.JLabel();
        tfPort1 = new javax.swing.JTextField();
        jLabel13 = new javax.swing.JLabel();
        tfDatabaseName1 = new javax.swing.JTextField();
        jLabel14 = new javax.swing.JLabel();
        tfUserName1 = new javax.swing.JTextField();
        jLabel15 = new javax.swing.JLabel();
        tfpassword1 = new javax.swing.JPasswordField();
        jPanel1 = new javax.swing.JPanel();
        jLabel1 = new javax.swing.JLabel();
        jLabel2 = new javax.swing.JLabel();
        jLabel3 = new javax.swing.JLabel();
        jLabel4 = new javax.swing.JLabel();
        jLabel5 = new javax.swing.JLabel();
        tfHost = new javax.swing.JTextField();
        tfPort = new javax.swing.JTextField();
        tfDatabaseName = new javax.swing.JTextField();
        tfUserName = new javax.swing.JTextField();
        tfpassword = new javax.swing.JPasswordField();
        jPanel6 = new javax.swing.JPanel();
        jProgressBar2 = new javax.swing.JProgressBar();
        lbGeneration = new javax.swing.JLabel();
        jPanel5 = new javax.swing.JPanel();
        jProgressBar1 = new javax.swing.JProgressBar();
        jPanel3 = new javax.swing.JPanel();
        bnMigrate = new javax.swing.JButton();
        bnCancel = new javax.swing.JButton();

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

        jPanel4.setBorder(javax.swing.BorderFactory.createTitledBorder("SoulToNGL Migration"));
        jPanel4.setPreferredSize(new java.awt.Dimension(487, 532));
        jPanel4.setLayout(new javax.swing.BoxLayout(jPanel4, javax.swing.BoxLayout.Y_AXIS));

        jPanel2.setBorder(javax.swing.BorderFactory.createTitledBorder("Source: SOUL Database connection details"));
        jPanel2.setLayout(new java.awt.GridBagLayout());

        jLabel11.setText("Host");
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.anchor = java.awt.GridBagConstraints.EAST;
        jPanel2.add(jLabel11, gridBagConstraints);

        tfHost1.setText("192.168.1.13");
        tfHost1.setPreferredSize(new java.awt.Dimension(132, 28));
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.gridx = 1;
        gridBagConstraints.gridy = 0;
        jPanel2.add(tfHost1, gridBagConstraints);

        jLabel12.setText("Port");
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.gridy = 1;
        gridBagConstraints.anchor = java.awt.GridBagConstraints.EAST;
        jPanel2.add(jLabel12, gridBagConstraints);

        tfPort1.setText("1433");
        tfPort1.setPreferredSize(new java.awt.Dimension(132, 28));
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.gridx = 1;
        gridBagConstraints.gridy = 1;
        jPanel2.add(tfPort1, gridBagConstraints);

        jLabel13.setText("Database name");
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.gridy = 2;
        gridBagConstraints.anchor = java.awt.GridBagConstraints.EAST;
        jPanel2.add(jLabel13, gridBagConstraints);

        tfDatabaseName1.setText("SOUL");
        tfDatabaseName1.setPreferredSize(new java.awt.Dimension(132, 28));
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.gridx = 1;
        gridBagConstraints.gridy = 2;
        jPanel2.add(tfDatabaseName1, gridBagConstraints);

        jLabel14.setText("User name");
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.gridx = 0;
        gridBagConstraints.gridy = 3;
        gridBagConstraints.anchor = java.awt.GridBagConstraints.EAST;
        jPanel2.add(jLabel14, gridBagConstraints);

        tfUserName1.setText("sa");
        tfUserName1.setPreferredSize(new java.awt.Dimension(132, 28));
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.gridx = 1;
        gridBagConstraints.gridy = 3;
        jPanel2.add(tfUserName1, gridBagConstraints);

        jLabel15.setText("Password");
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.gridy = 4;
        gridBagConstraints.anchor = java.awt.GridBagConstraints.EAST;
        jPanel2.add(jLabel15, gridBagConstraints);

        tfpassword1.setText("newgenlib");
        tfpassword1.setPreferredSize(new java.awt.Dimension(132, 28));
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.gridx = 1;
        jPanel2.add(tfpassword1, gridBagConstraints);

        jPanel4.add(jPanel2);

        jPanel1.setBorder(javax.swing.BorderFactory.createTitledBorder("Destination: NGL Database connection details"));
        jPanel1.setLayout(new java.awt.GridBagLayout());

        jLabel1.setText("Host");
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.anchor = java.awt.GridBagConstraints.EAST;
        jPanel1.add(jLabel1, gridBagConstraints);

        jLabel2.setText("Port");
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.gridy = 1;
        gridBagConstraints.anchor = java.awt.GridBagConstraints.EAST;
        jPanel1.add(jLabel2, gridBagConstraints);

        jLabel3.setText("Database name");
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.gridy = 2;
        gridBagConstraints.anchor = java.awt.GridBagConstraints.EAST;
        jPanel1.add(jLabel3, gridBagConstraints);

        jLabel4.setText("User name");
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.gridx = 0;
        gridBagConstraints.gridy = 3;
        gridBagConstraints.anchor = java.awt.GridBagConstraints.EAST;
        jPanel1.add(jLabel4, gridBagConstraints);

        jLabel5.setText("Password");
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.gridy = 4;
        gridBagConstraints.anchor = java.awt.GridBagConstraints.EAST;
        jPanel1.add(jLabel5, gridBagConstraints);

        tfHost.setText("192.168.1.5");
        tfHost.setPreferredSize(new java.awt.Dimension(132, 28));
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.gridx = 1;
        gridBagConstraints.gridy = 0;
        jPanel1.add(tfHost, gridBagConstraints);

        tfPort.setText("5432");
        tfPort.setPreferredSize(new java.awt.Dimension(132, 28));
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.gridx = 1;
        gridBagConstraints.gridy = 1;
        jPanel1.add(tfPort, gridBagConstraints);

        tfDatabaseName.setText("ASoulToNGL");
        tfDatabaseName.setPreferredSize(new java.awt.Dimension(132, 28));
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.gridx = 1;
        gridBagConstraints.gridy = 2;
        jPanel1.add(tfDatabaseName, gridBagConstraints);

        tfUserName.setText("newgenlib");
        tfUserName.setPreferredSize(new java.awt.Dimension(132, 28));
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.gridx = 1;
        gridBagConstraints.gridy = 3;
        jPanel1.add(tfUserName, gridBagConstraints);

        tfpassword.setText("newgenlib");
        tfpassword.setPreferredSize(new java.awt.Dimension(132, 28));
        gridBagConstraints = new java.awt.GridBagConstraints();
        gridBagConstraints.gridx = 1;
        jPanel1.add(tfpassword, gridBagConstraints);

        jPanel4.add(jPanel1);

        jPanel6.setBorder(javax.swing.BorderFactory.createTitledBorder("Overall Progress"));
        jPanel6.setLayout(new java.awt.BorderLayout());
        jPanel6.add(jProgressBar2, java.awt.BorderLayout.CENTER);
        jPanel6.add(lbGeneration, java.awt.BorderLayout.PAGE_START);

        jPanel4.add(jPanel6);

        jPanel5.setBorder(javax.swing.BorderFactory.createTitledBorder("Current Record Progress"));
        jPanel5.setLayout(new java.awt.BorderLayout());
        jPanel5.add(jProgressBar1, java.awt.BorderLayout.CENTER);

        jPanel4.add(jPanel5);

        bnMigrate.setText("Migrate");
        bnMigrate.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                bnMigrateActionPerformed(evt);
            }
        });
        jPanel3.add(bnMigrate);

        bnCancel.setText("Cancel");
        bnCancel.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                bnCancelActionPerformed(evt);
            }
        });
        jPanel3.add(bnCancel);

        jPanel4.add(jPanel3);

        getContentPane().add(jPanel4, java.awt.BorderLayout.CENTER);

        pack();
    }// </editor-fold>//GEN-END:initComponents

    private void bnMigrateActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_bnMigrateActionPerformed
        // TODO add your handling code here:
        jProgressBar2.setMinimum(0);
        jProgressBar2.setMaximum(3);
        jProgressBar2.setStringPainted(true);
        jProgressBar2.setString("0/3");
        bnMigrate.setEnabled(false);
        displayDbProperties dp = new displayDbProperties(this);
        Thread td = new Thread(dp, "displayDbProperties");
        td.start();
    }//GEN-LAST:event_bnMigrateActionPerformed

    private void bnCancelActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_bnCancelActionPerformed
        // TODO add your handling code here:
        System.exit(0);
    }//GEN-LAST:event_bnCancelActionPerformed

    /**
     * @param args the command line arguments
     */
    public static void main(String args[]) {
        java.awt.EventQueue.invokeLater(new Runnable() {

            public void run() {
                new SoulToNGLTool().setVisible(true);
            }
        });
    }
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton bnCancel;
    public javax.swing.JButton bnMigrate;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel11;
    private javax.swing.JLabel jLabel12;
    private javax.swing.JLabel jLabel13;
    private javax.swing.JLabel jLabel14;
    private javax.swing.JLabel jLabel15;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JLabel jLabel3;
    private javax.swing.JLabel jLabel4;
    private javax.swing.JLabel jLabel5;
    private javax.swing.JPanel jPanel1;
    private javax.swing.JPanel jPanel2;
    private javax.swing.JPanel jPanel3;
    private javax.swing.JPanel jPanel4;
    private javax.swing.JPanel jPanel5;
    private javax.swing.JPanel jPanel6;
    public javax.swing.JProgressBar jProgressBar1;
    public javax.swing.JProgressBar jProgressBar2;
    public javax.swing.JLabel lbGeneration;
    public javax.swing.JTextField tfDatabaseName;
    public javax.swing.JTextField tfDatabaseName1;
    public javax.swing.JTextField tfHost;
    public javax.swing.JTextField tfHost1;
    public javax.swing.JTextField tfPort;
    public javax.swing.JTextField tfPort1;
    public javax.swing.JTextField tfUserName;
    public javax.swing.JTextField tfUserName1;
    public javax.swing.JPasswordField tfpassword;
    public javax.swing.JPasswordField tfpassword1;
    // End of variables declaration//GEN-END:variables

    private String getConnectionUrl() {
//        System.out.println(url + serverName + ":" + portNumber + ";databaseName=" + databaseName + ";selectMethod=" + selectMethod + ";");
        return "jdbc:sqlserver://" + tfHost1.getText() + ":" + tfPort1.getText() + ";databaseName=" + tfDatabaseName1.getText();
    }

    public java.sql.Connection getConnection() {
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            con = java.sql.DriverManager.getConnection(this.getConnectionUrl(), tfUserName1.getText(), String.valueOf(tfpassword1.getPassword()));
            if (con != null) {
                System.out.println("Connection Successful!");
            }
        } catch (Exception e) {
//            System.out.println("jdbc:sqlserver://" + tfHost1.getText() + ":" + tfPort1.getText() + ";databaseName=" + tfDatabaseName1.getText() + "," + tfUserName1.getText() + "," + tfpassword1.getText());
            javax.swing.JOptionPane.showMessageDialog(null, "Sqlserver connection refused", "error", JOptionPane.ERROR_MESSAGE);
//
//            e.printStackTrace();
//            System.out.println("Error Trace in getConnection() : " + e.getMessage());
        }
        return con;
    }
}

class displayDbProperties implements Runnable {

    SoulToNGLTool hostName;
    PersistMARCRecord pmarc;
    private java.sql.Connection con = null;
    int in = 1;
    int in1 = 1;
    int maxal = 0;
    int maxval = 0;
    int ti = 1;

    public displayDbProperties(SoulToNGLTool hostName) {
        this.hostName = hostName;
    }

    @Override
    public void run() {

        java.sql.ResultSet rs = null;
        try {
            final int count;
            Class.forName("org.postgresql.Driver");
            Connection conn=null;
            try {
                System.out.println("jdbc:postgresql://" + hostName.tfHost.getText() + ":" + hostName.tfPort.getText() + "/" + hostName.tfDatabaseName.getText() + "," + hostName.tfUserName.getText() + "," + String.valueOf(hostName.tfpassword.getPassword()));
               conn = DriverManager.getConnection("jdbc:postgresql://" + hostName.tfHost.getText() + ":" + hostName.tfPort.getText() + "/" + hostName.tfDatabaseName.getText(), hostName.tfUserName.getText(), String.valueOf(hostName.tfpassword.getPassword()));
            } catch (Exception e) {
                javax.swing.JOptionPane.showMessageDialog(null, "postgresql server connection refused", "error", JOptionPane.ERROR_MESSAGE);
                System.exit(0);
            }
            con = hostName.getConnection();
            pmarc = new PersistMARCRecord("1", "1", "1", "1", "1", conn, false);
            pmarc.setPOSTGRESQL_VERSION("8.3");
            if (con != null) {
                hostName.jProgressBar1.setMinimum(0);
                hostName.jProgressBar1.setStringPainted(true);
                hostName.jProgressBar2.setStringPainted(true);
                hostName.jProgressBar2.setString("0/3");
                hostName.lbGeneration.setText("Importing biblographic data");
                Statement s1t = con.createStatement();
                ResultSet r1s = s1t.executeQuery("select count(*) from bk_mst");
                int count1 = 0;
                while (r1s.next()) {
                    count1 = r1s.getInt(1);
                }
                s1t.close();
                r1s.close();
                count = count1;
                hostName.jProgressBar1.setMaximum(count);
                System.out.println("**************count************" + count);

                Statement stmt = con.createStatement();
                rs = stmt.executeQuery("select * from bk_mst");

                while (rs.next()) {
                    if (in < count) {
                        String recno = rs.getString("bk_recno");
                        System.out.println("\n*********************************************************" + recno);
                        String accno = "";
                        String location = "";
                        String authfname = "";
                        String authlname = "";
                        String authmstfname = "";
                        String authmostlname = "";
                        String classno = "";
                        String bookno = "";
                        String corpname = "";
                        String isbn = "";
                        String language = "";
                        String locaton = "";
                        String dentry = "";
                        String title = "";
                        String edition = "";
                        String price = "";
                        String phydscr = "";
                        String bibnote = "";
                        String bk_note = "";
                        String publisher = "";
                        String place = "";
                        String year = "";
                        String bk_year = "";
                        String subject = "";

                        Statement stmt1 = con.createStatement();
                        ResultSet rs1 = stmt1.executeQuery("select bk_accno,bk_location from bk_accession where bk_recno=" + recno);
                        while (rs1.next()) {
                            accno = rs1.getString(1);
                            location = rs1.getString(2);
                            if (accno == null) {
                                accno = "";
                            }
                            if (location == null) {
                                location = "";
                            }

                        }

                        Statement stmt2 = con.createStatement();
                        ResultSet rs2 = stmt2.executeQuery("select bk_fname,bk_lname from bk_auth where bk_recno=" + recno);
                        while (rs2.next()) {
                            authfname = rs2.getString(1);
                            authlname = rs2.getString(2);
                            if (authfname == null) {
                                authfname = "";
                            }
                            if (authlname == null) {
                                authlname = "";
                            }
                        }

                        Statement stmt3 = con.createStatement();
                        ResultSet rs3 = stmt3.executeQuery("select bk_fname,bk_lname from bk_authmst where bk_recno=" + recno);
                        while (rs3.next()) {
                            authmstfname = rs3.getString(1);
                            authmostlname = rs3.getString(2);
                            if (authmstfname == null) {
                                authmstfname = "";
                            }
                            if (authmostlname == null) {
                                authmostlname = "";
                            }

                        }
                        Statement stmt4 = con.createStatement();
                        ResultSet rs4 = stmt4.executeQuery("select bk_classno,bk_bookno from bk_classno where bk_recno=" + recno);
                        while (rs4.next()) {
                            classno = rs4.getString(1);
                            bookno = rs4.getString(2);
                            if (classno == null) {
                                classno = "";
                            }
                            if (bookno == null) {
                                bookno = "";
                            }

                        }
                        Statement stmt5 = con.createStatement();
                        ResultSet rs5 = stmt5.executeQuery("select bk_name from bk_corp where bk_recno=" + recno);
                        while (rs5.next()) {
                            corpname = rs5.getString(1);
                            if (corpname == null) {
                                corpname = "";
                            }
                        }

                        Statement stmt6 = con.createStatement();
                        ResultSet rs6 = stmt6.executeQuery("select bk_isbn from bk_isbn where bk_recno=" + recno);
                        while (rs6.next()) {
                            isbn = rs6.getString(1);
                            if (isbn == null) {
                                isbn = "";
                            }
                        }
                        Statement stmt7 = con.createStatement();
                        ResultSet rs7 = stmt7.executeQuery("select bk_loc,bk_dentry,bk_title,bk_edition,bk_dt_pbl,bk_price,bk_phydscr,bk_bibnote from bk_mst where bk_recno=" + recno);
                        while (rs7.next()) {
                            locaton = rs7.getString(1);
                            dentry = rs7.getString(2);
                            title = rs7.getString(3);
                            edition = rs7.getString(4);
                            year = rs7.getString(5);
                            price = rs7.getString(6);
                            phydscr = rs7.getString(7);
                            bibnote = rs7.getString(8);
                            if (locaton == null) {
                                locaton = "";
                            }
                            if (dentry == null) {
                                dentry = "";
                            }
                            if (title == null) {
                                title = "";
                            }
                            if (edition == null) {
                                edition = "";
                            }
                            if (year == null) {
                                year = "";
                            }
                            if (price == null) {
                                price = "";
                            }
                            if (phydscr == null) {
                                phydscr = "";
                            }
                            if (bibnote == null) {
                                bibnote = "";
                            }
                        }
                        Statement stmt8 = con.createStatement();
                        ResultSet rs8 = stmt8.executeQuery("select bk_note from bk_note where bk_recno=" + recno);
                        while (rs8.next()) {
                            bk_note = rs8.getString(1);
                            if (bk_note == null) {
                                bk_note = "";
                            }
                        }

                        Statement stmt9 = con.createStatement();
                        ResultSet rs9 = stmt9.executeQuery("select bk_publisher,bk_place,bk_year from bk_pbldtl where bk_recno=" + recno);
                        while (rs9.next()) {
                            publisher = rs9.getString(1);
                            place = rs9.getString(2);
                            bk_year = rs9.getString(3);
                            if (publisher == null) {
                                publisher = "";
                            }
                            if (place == null) {
                                place = "";
                            }
                            if (bk_year == null) {
                                bk_year = "";
                            }


                        }

                        Statement stmt10 = con.createStatement();
                        ResultSet rs10 = stmt10.executeQuery("select a.bk_subject from bk_subject a, bk_classno b where b.bk_recno=" + recno + " and a.bk_classno=b.bk_classno");
                        while (rs10.next()) {
                            subject = rs10.getString(1);
                            if (subject == null) {
                                subject = "";
                            }
                        }

                        rs10.close();
                        stmt10.close();
                        rs9.close();
                        stmt9.close();
                        rs8.close();
                        stmt8.close();
                        rs7.close();
                        stmt7.close();
                        rs6.close();
                        stmt6.close();
                        rs5.close();
                        stmt5.close();
                        rs4.close();
                        stmt4.close();
                        rs3.close();
                        stmt3.close();
                        rs2.close();
                        stmt2.close();
                        rs1.close();
                        stmt1.close();
                        authfname = authfname.trim();
                        authlname = authlname.trim();
                        authmostlname = authmostlname.trim();
                        authmstfname = authmstfname.trim();
                        if (authmstfname != null && !authmstfname.equals("")) {
                            int autlent = authmstfname.length() - 1;
                            String lastone = authmstfname.substring(autlent);
                            if (lastone.equals(".")) {

                                authmstfname = authmstfname.substring(0, authmstfname.length() - 1);
                            }
                        }

                        if (authfname != null && !authfname.equals("")) {
                            int a = authfname.length() - 1;
                            String l1 = authfname.substring(a);
                            if (l1.equals(".")) {
                                authfname = authfname.substring(0, authfname.length() - 1);
                            }
                        }
                        String author="";
                        String author1="";

                        if (authfname == null && authfname.equals("") ) {
                            if(authlname!=null && !authlname.trim().equals("")){
                            author = authlname;
                            }
                        }

                        if (authlname == null && authlname.equals("") ) {
                            if(authfname!=null && !authfname.trim().equals("")){
                            author = authfname+".";
                            }
                        }

                        if (authlname != null && !authlname.equals("") ) {
                            if(authfname!=null && !authfname.trim().equals("")){
                        author = authlname + ", " + authfname + ".";
                            }
                        }

                        if (authmstfname == null && authmstfname.equals("") ) {
                            if(authmostlname!=null && !authmostlname.trim().equals("")){
                            author1 = authmostlname;
                            }
                        }

                        if (authmostlname == null && authmostlname.equals("") ) {
                            if(authmstfname!=null && !authmstfname.trim().equals("")){
                            author1 = authmstfname+".";
                            }
                        }

                        if (authmostlname != null && !authmostlname.equals("") ) {
                            if(authmstfname!=null && !authmstfname.trim().equals("")){
                        author1 = authmostlname + ", " + authmstfname + ".";
                            }
                        }


                        mapToMarcAndSave(accno, classno, location, author, publisher, language, year, edition, phydscr, price, bookno, corpname, isbn, locaton, dentry, title, bibnote, place, subject, author1);
                        in++;
                        hostName.jProgressBar1.setValue(in);
                        hostName.jProgressBar1.setString(in + "/" + count);
//                    hostName.jProgressBar1.repaint();
                    } else if (in == count) {
                        hostName.jProgressBar1.setValue(0);
                        hostName.jProgressBar2.setValue(1);
                        hostName.jProgressBar2.setString("1/3");
                    }


                }

                rs.close();
                stmt.close();
                rs = null;
                closeConnection();




            } else {
                System.out.println("Error: No active Connection");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }


        /*****************************************************************************************************/
        try {


            int in = 0;
            int count = 0;
            int count1 = 0;
            int count2 = 0;
            try {
                con = hostName.getConnection();
                hostName.jProgressBar1.setMinimum(0);
                Statement st1 = con.createStatement();
                ResultSet rst = st1.executeQuery("select count(*) from m_member");
                while (rst.next()) {
                    count1 = rst.getInt(1);
                }
                System.out.println("*****************count" + count1);
                st1.close();
                rst.close();

                Statement stmnt1 = con.createStatement();
                ResultSet rst1 = stmnt1.executeQuery("select count(*) from m_user");
                while (rst1.next()) {
                    count2 = rst1.getInt(1);
                }
                count = count1 + count2;
                System.out.println("*****************count" + count2);
                System.out.println("*****************Total count" + count);

                stmnt1.close();
                rst1.close();

                hostName.jProgressBar1.setMaximum(count);
                hostName.jProgressBar1.setStringPainted(true);

            } catch (Exception ec) {
                ec.printStackTrace();
            }

            try {
                con = hostName.getConnection();
                Statement stmt = con.createStatement();
                ResultSet rwsu1 = stmt.executeQuery("select * from m_member");
                ResultSet rwsu11 = null;
                ResultSet rwsu12 = null;
                hostName.lbGeneration.setText("Importing patron data");
                while (rwsu1.next()) {
                    if (in < count1) {
                        System.out.println(rwsu1.getString("mem_cd"));
                        String patronId = "";
                        String patronName = "";
                        String memStartDate = "";
                        String modifiedStartDate = "";
                        String memEndDate = "";
                        String modifiedEndDate = "";
                        String category = "";
                        String department = "";
                        String course = "";
                        String email = "";
                        String address1 = "";
                        String address2 = "";
                        String city = "";
                        String state = "";
                        String country = "";
                        String zipCode = "";
                        String phone = "";
                        String mobile = "";


                        patronId = rwsu1.getString("mem_cd");
                        String fname = rwsu1.getString("mem_firstnm");
                        String mname = rwsu1.getString("mem_midnm");
                        String lname = rwsu1.getString("mem_lstnm");
                        patronName = lname + ", " + mname + ", " + fname;
                        memStartDate = rwsu1.getString("mem_efctv_dt");
                        memEndDate = rwsu1.getString("mem_effctupto");
                        email = rwsu1.getString("mem_email");
                        address1 = rwsu1.getString("mem_prmntadd1");
                        address2 = rwsu1.getString("mem_prmntadd2");
                        city = rwsu1.getString("mem_prmntcity");
                        zipCode = rwsu1.getString("mem_prmntpin");
                        phone = rwsu1.getString("mem_prmntphone");



                        String cat_mem = rwsu1.getString("mem_ctgry");
                        Statement stmt1 = con.createStatement();
                        rwsu11 = stmt1.executeQuery("select ctgry_desc from m_ctgry where ctgry_cd='" + cat_mem + "'");
//                    System.out.println("select ctgry_desc from m_ctgry where ctgry_cd='" + cat_mem + "'");
                        while (rwsu11.next()) {
                            category = rwsu11.getString(1);
//                        System.out.println(category);
                        }
                        Statement stmt2 = con.createStatement();
                        rwsu12 = stmt2.executeQuery("select a.branch_descr from m_branch a,m_member b where a.branch_cd=b.mem_degree and a.fclty_cd=b.mem_dept and mem_cd='" + patronId + "'");
//                    System.out.println("select a.branch_descr from m_branch a,m_member b where a.branch_cd=b.mem_degree and a.fclty_cd=b.mem_dept and mem_cd=" + patronId);
                        while (rwsu12.next()) {
                            department = rwsu12.getString(1);
//                        System.out.println(department);
                        }
//                    System.out.println(patronId+ "---1------" +  patronName+ "----2-----" +  memStartDate+ "---3------" +  modifiedStartDate+ "----4-----" +  memEndDate+ "----5-----" +  modifiedEndDate+ "-----6----" +  category+ "------7---" +  department+ "-----8----" +  course+ "----9-----" +  email+ "---10------" +  address1+ "----11-----" +  address2+ "----12-----" +  city+ "----13-----" +  state+ "---14------" +  country+ "---15------" +  zipCode+ "---16------" +  phone+ "----17-----" +  mobile);
//                    dataEnter(patronId, patronName, memStartDate, modifiedStartDate, memEndDate, modifiedEndDate, category, department, course, email, address1, address2, city, state, country, zipCode, phone, mobile);
                        dataEnter(patronId, patronName, memStartDate, modifiedStartDate, memEndDate, modifiedEndDate, category, department, course, email, address1, address2, city, state, country, zipCode, phone, mobile, "B");
                        in++;
                        hostName.jProgressBar1.setValue(in);
                        hostName.jProgressBar1.setString(in + "/" + count);
                        rwsu12.close();
                        stmt2.close();
                        rwsu11.close();
                        stmt1.close();
                    }
                }
                rwsu1.close();
                stmt.close();
                con.close();


            } catch (Exception e) {
                e.printStackTrace();
            }


            try {

                con = hostName.getConnection();
                Statement stmt = con.createStatement();
                ResultSet rws1 = stmt.executeQuery("select * from m_user");
                int in1 = 0;
                int value = 0;
                while (rws1.next()) {
                    if (in1 < count2) {

                        System.out.println(rws1.getString("user_cd"));
                        String patronId = "";
                        String patronName = "";

                        patronId = rws1.getString("user_cd");
                        String fname = rws1.getString("user_firstnm");
                        String mname = rws1.getString("user_midnm");
                        String lname = rws1.getString("user_lstnm");
                        patronName = fname + " " + mname + " " + lname;

                        System.out.println("patron id ***********" + patronId + "patron name******************" + patronName);
                        dataEnter(patronId, patronName, "2011-06-09 00:00:00", "", "2015-06-09 00:00:00", "", "", "Library", "", "", "", "", "", "", "", "", "", "", "A");
                        in1++;
                        value = count1 + in1;
                        hostName.jProgressBar1.setValue(value);
                        hostName.jProgressBar1.setString(value + "/" + count);

                    }
                    if (value == count) {
                        hostName.jProgressBar1.setValue(0);
                        hostName.jProgressBar2.setValue(2);
                        hostName.jProgressBar2.setString("2/3");
                    }
                }
                rws1.close();
                stmt.close();
                con.close();


            } catch (Exception e) {
                e.printStackTrace();
            }



        } catch (Exception ex) {
        }




        try {

            con = hostName.getConnection();
            if (con != null) {
                Statement st21 = con.createStatement();
                ResultSet re21 = st21.executeQuery("SELECT count(*) FROM dbo.t_issue WHERE NOT EXISTS (SELECT * FROM dbo.t_receive WHERE dbo.t_receive.mem_cd = t_issue.mem_cd and dbo.t_receive.accn_no=dbo.t_issue.acc_no and dbo.t_receive.iss_dt=dbo.t_issue.iss_dt)");
                int in = 0;
                while (re21.next()) {
                    in = re21.getInt(1);
                }
                re21.close();
                st21.close();
                hostName.jProgressBar1.setMaximum(in);
                hostName.jProgressBar2.setMinimum(0);
                hostName.jProgressBar2.setMaximum(3);
                hostName.lbGeneration.setText("Importing circulation data");
                Statement stmt = con.createStatement();
                ResultSet resut = stmt.executeQuery("SELECT * FROM dbo.t_issue WHERE NOT EXISTS (SELECT * FROM dbo.t_receive WHERE dbo.t_receive.mem_cd = t_issue.mem_cd and dbo.t_receive.accn_no=dbo.t_issue.acc_no and dbo.t_receive.iss_dt=dbo.t_issue.iss_dt)");

                int i = 0;
                while (resut.next()) {
                    if (i < in) {

                        String mem_cd = resut.getString("mem_cd");
                        String acc_no = resut.getString("acc_no");
                        String iss_dt = resut.getString("iss_dt");
                        String user_cd = resut.getString("user_cd");
                        String due_dt = resut.getString("due_dt");
                        System.out.println(mem_cd + "**" + acc_no + "**" + iss_dt + "**" + user_cd + "**" + due_dt);
                        dataEntered(mem_cd, acc_no, iss_dt, user_cd, due_dt);
                        i++;
                        hostName.jProgressBar1.setStringPainted(true);
                        hostName.jProgressBar1.setValue(i);
                        hostName.jProgressBar1.setString(i + "/" + in);
                    }
                    if (i == in) {
                        hostName.jProgressBar2.setValue(3);
                        hostName.jProgressBar2.setString("3/3");
                        JOptionPane.showMessageDialog(null, "Soul To NGL data conversion is finished");
                        hostName.bnMigrate.setEnabled(true);
                        hostName.jProgressBar1.setValue(0);
                        hostName.jProgressBar1.setString("");
                        hostName.lbGeneration.setText("");
                        hostName.jProgressBar2.setValue(0);
                        hostName.jProgressBar2.setString("");
//                        System.exit(0);
                    }
                }
                resut.close();
                stmt.close();
                con.close();


            }
        } catch (Exception e) {
            e.printStackTrace();
        }


        /*****************************************************************************************************/
    }

    public void dataEnter(String patronId, String patronName, String memStartDate, String modifiedStartDate, String memEndDate, String modifiedEndDate, String category, String department, String course, String email, String address1, String address2, String city, String state, String country, String zipCode, String phone, String mobile, String patron) {

        try {
            String libId = "1";
            Class.forName("org.postgresql.Driver");
            Connection conn = null;
            try {
                System.out.println("jdbc:postgresql://" + hostName.tfHost.getText() + ":" + hostName.tfPort.getText() + "/" + hostName.tfDatabaseName.getText() + "," + hostName.tfUserName.getText() + "," + String.valueOf(hostName.tfpassword.getPassword()));
                conn = DriverManager.getConnection("jdbc:postgresql://" + hostName.tfHost.getText() + ":" + hostName.tfPort.getText() + "/" + hostName.tfDatabaseName.getText(), hostName.tfUserName.getText(), String.valueOf(hostName.tfpassword.getPassword()));
            } catch (Exception e) {
                javax.swing.JOptionPane.showMessageDialog(null, "postgresql server connection refused", "error", JOptionPane.ERROR_MESSAGE);
                System.exit(0);
            }
            int inc = 0;

//            int patIdIndex = 0, patNameIndex = 0, memStartDateIndex = 0, memEndDateIndex = 0, categoryIndex = 0, departmentIndex = 0, courseIndex = 0, emailIndex = 0, address1Index = 0, address2Index = 0, cityIndex = 0, stateIndex = 0, countryIndex = 0, zipcodeIndex = 0, phoneIndex = 0, mobileIndex = 0;
            inc++;
            if (conn != null) {
                String query = "";
                String ptrnId = "";
                int deptId = 0;
                int categoryId = 0;
                int courseId = 0;
                if (patronId != null) {
                    Statement stmt = conn.createStatement();
                    ResultSet rs = stmt.executeQuery("select patron_id from patron where patron_id = '" + patronId + "' and library_id = '" + libId + "'");
                    while (rs.next()) {
                        ptrnId = rs.getString("patron_id");
                    }
                    rs.close();
                    stmt.close();
                }
                if (department != null) {
                    int dCount = 0, dInc = 0;
                    String oldDept = "";
                    Statement stmt = conn.createStatement();
                    ResultSet rs = stmt.executeQuery("select dept_name from dept where dept_name = '" + department + "' and library_id='" + libId + "'");
                    while (rs.next()) {
                        oldDept = rs.getString("dept_name");
                    }
                    rs.close();
                    if (!department.equalsIgnoreCase(oldDept)) {
                        Statement stmt1 = conn.createStatement();
                        ResultSet rs1 = stmt1.executeQuery("select max(dept_id) from dept");
                        while (rs1.next()) {
                            dCount = rs1.getInt(1);
                        }
                        dInc = dCount + 1;
                        rs1.close();
//                    stmt1.executeUpdate("INSERT INTO dept(library_id, dept_id, dept_name, entry_id, entry_date) VALUES ('" + libId + "', " + dInc + ", '" + department + "', '" + 1 + "', current_date)");
                        System.out.println("INSERT INTO dept(library_id, dept_id, dept_name, entry_id, entry_date) VALUES ('" + libId + "', " + dInc + ", '" + department + "', '1', current_date)");
                        stmt1.executeUpdate("INSERT INTO dept(library_id, dept_id, dept_name,entry_id, entry_date) VALUES ('" + libId + "', " + dInc + ", '" + department + "','1', current_date)");
                        stmt1.close();
                    }
                    rs = stmt.executeQuery("select dept_id from dept where dept_name = '" + department + "' and library_id = '" + libId + "'");
                    while (rs.next()) {
                        deptId = rs.getInt("dept_id");
                    }
                    rs.close();
                    stmt.close();
                }
                if (category != null) {
                    int pcCount = 0, pcInc = 0;
                    String oldPatCategory = "";
                    Statement stmt = conn.createStatement();
                    ResultSet rs = stmt.executeQuery("select patron_category_name from patron_category where patron_category_name = '" + category + "' and library_id='" + libId + "'");
                    while (rs.next()) {
                        oldPatCategory = rs.getString("patron_category_name");
                    }
                    rs.close();
                    if (!category.equalsIgnoreCase(oldPatCategory)) {
                        Statement stmt1 = conn.createStatement();
                        ResultSet rs1 = stmt1.executeQuery("select max(patron_category_id) from patron_category");
                        while (rs1.next()) {
                            pcCount = rs1.getInt(1);
                        }
                        pcInc = pcCount + 1;
                        rs1.close();
                        stmt1.executeUpdate("INSERT INTO patron_category(patron_category_id, library_id, patron_category_name, ill_thru_net, renewal_thru_net, entry_date) VALUES ('" + pcInc + "', '" + libId + "', '" + category + "', '', '', current_date)");
                        System.out.println("INSERT INTO patron_category(patron_category_id, library_id, patron_category_name, ill_thru_net, renewal_thru_net, entry_date) VALUES ('" + pcInc + "', '" + libId + "', '" + category + "', '', '', current_date)");
                        stmt1.close();
                    }
                    rs = stmt.executeQuery("select patron_category_id from patron_category where patron_category_name = '" + category + "' and library_id = '" + libId + "'");
                    while (rs.next()) {
                        categoryId = rs.getInt("patron_category_id");
                    }
                    rs.close();
                    stmt.close();
                }
                if (course != null) {
                    int cCount = 0, cInc = 0;
                    String oldCourse = "";
                    Statement stmt = conn.createStatement();
                    ResultSet rs = stmt.executeQuery("select course_name from course where course_name = '" + course + "' and library_id='" + libId + "'");
                    while (rs.next()) {
                        oldCourse = rs.getString("course_name");
                    }
                    rs.close();
                    if (!course.equalsIgnoreCase(oldCourse)) {
                        Statement stmt1 = conn.createStatement();
                        ResultSet rs1 = stmt1.executeQuery("select max(course_id) from course");
                        while (rs1.next()) {
                            cCount = rs1.getInt(1);
                        }
                        cInc = cCount + 1;
                        rs1.close();
//                    stmt1.executeUpdate("INSERT INTO course(library_id, course_id, course_name, entry_id, entry_date) VALUES ('" + libId + "', '" + cInc + "', '" + course + "', '" + 1 + "', current_date)");
                        stmt1.executeUpdate("INSERT INTO course(library_id, course_id, course_name, entry_id, entry_date) VALUES ('" + libId + "', '" + cInc + "', '" + course + "', ' ', current_date)");
                        System.out.println("INSERT INTO course(library_id, course_id, course_name, entry_id, entry_date) VALUES ('" + libId + "', '" + cInc + "', '" + course + "', ' ', current_date)");
                        stmt1.close();
                    }
                    rs = stmt.executeQuery("select course_id from course where course_name = '" + course + "' and library_id = '" + libId + "'");
                    while (rs.next()) {
                        courseId = rs.getInt("course_id");
                    }
                    rs.close();
                    stmt.close();
                }
                if (patronId.equals(ptrnId)) {
                    //query = "UPDATE patron SET library_id='" + libId + "', patron_id='" + patronId + "', patron_category_id='" + categoryId + "', dept_id='" + deptId + "', fname='" + patronName + "', address1='" + address1 + "', address2='" + address2 + "', city='" + city + "', state='" + state + "', country='" + country + "', pin='" + zipCode + "', phone1='" + phone + "', phone2='" + mobile + "', email='" + email + "', membership_start_date='" + modifiedStartDate + "', membership_expiry_date='" + modifiedEndDate + "', course_id='" + courseId + "' WHERE patron_id = '" + ptrnId + "' and library_id = '" + libId + "'";
                    query = "UPDATE patron SET library_id='" + libId + "'";
                    if (categoryId != 0) {
                        if (patron.equals("B")) {
                            query += ", patron_category_id=" + categoryId;
                        } else {
                            query += ", patron_category_id= 1";
                        }
                    }
                    if (deptId != 0) {
                        query += ", dept_id=" + deptId;
                    }
                    if (courseId != 0) {
                        query += ", course_id=" + courseId;
                    }
                    if (!patronName.equals("")) {
                        query += ", fname='" + patronName + "'";
                    }
                    if (!memStartDate.equals("")) {
                        query += ", membership_start_date='" + memStartDate + "'";
                        System.out.println("************************" + memStartDate + "" + memEndDate);
                    }
                    if (!memEndDate.equals("")) {
                        query += ", membership_expiry_date='" + memEndDate + "'";
                    }
                    if (email != null && !email.equals("")) {
                        query += ", email='" + email + "'";
                    }
                    if (!address1.equals("")) {
                        query += ", address1='" + address1 + "'";
                    }
                    if (!address2.equals("")) {
                        query += ", address2='" + address2 + "'";
                    }
                    if (!city.equals("")) {
                        query += ", city='" + city + "'";
                    }
                    if (!state.equals("")) {
                        query += ", state='" + state + "'";
                    }
                    if (!country.equals("")) {
                        query += ", country='" + country + "'";
                    }
                    if (!zipCode.equals("")) {
                        query += ", pin='" + zipCode + "'";
                    }
                    if (phone != null && !phone.equals("")) {
                        query += ", phone1='" + phone + "'";
                    }
                    if (!mobile.equals("")) {
                        query += ", phone2='" + mobile + "'";
                    }
                    query += " WHERE patron_id = '" + ptrnId + "' and library_id = '" + libId + "'";
                } else {
                    String column = "";
                    String values = "";
                    column = "library_id";
                    values = libId;
                    column += ", patron_id";
                    if (!patronId.equals("")) {
                        values += ", '" + patronId + "'";
                    }
                    column += ", patron_category_id";
                    if (patron.equals("B")) {
                        if (categoryId != 0) {
                            values += ", " + categoryId;
                        } else {
                            values += ", ";
                        }
                    } else {
                        values += ", 1";
                    }
                    column += ", isonline";
                    values += ", 'A'";
                    column += ", owns";
                    values += ", ''";
                    column += ", created_on";
                    values += ", current_date";
                    column += ", patron_type";
                    if (patron.equals("B")) {
                        values += ", 'B'";
                    } else {
                        values += ", 'A'";
                    }
                    column += ", dept_id";
                    if (deptId != 0) {
                        values += ", " + deptId;
                    }
                    column += ", fname";
                    if (!patronName.equals("")) {
                        values += ", '" + patronName + "'";
                    }
                    column += ", mname";
                    values += ", ''";
                    column += ", lname";
                    values += ", ''";
                    column += ", address1";
                    if (!address1.equals("")) {
                        values += ", '" + address1 + "'";
                    } else {
                        values += ", ''";
                    }
                    column += ", address2";
                    if (!address2.equals("")) {
                        values += ", '" + address2 + "'";
                    } else {
                        values += ", ''";
                    }
                    column += ", city";
                    if (!city.equals("")) {
                        values += ", '" + city + "'";
                    } else {
                        values += ", ''";
                    }
                    column += ", state";
                    if (!state.equals("")) {
                        values += ", '" + state + "'";
                    } else {
                        values += ", ''";
                    }
                    column += ", country";
                    if (!country.equals("")) {
                        values += ", '" + country + "'";
                    } else {
                        values += ", ''";
                    }
                    column += ", pin";
                    if (!zipCode.equals("")) {
                        values += ", '" + zipCode + "'";
                    } else {
                        values += ", ''";
                    }
                    column += ", phone1";
                    if (phone != null && !phone.equals("")) {
                        values += ", '" + phone + "'";
                    } else {
                        values += ", ''";
                    }
                    column += ", phone2";
                    if (!mobile.equals("")) {
                        values += ", '" + mobile + "'";
                    } else {
                        values += ", ''";
                    }
                    column += ", fax";
                    values += ", ''";
                    column += ", email";
                    if (email != null && !email.equals("")) {
                        values += ", '" + email + "'";
                    } else {
                        values += ", ''";
                    }
                    column += ", paddress1";
                    values += ", ''";
                    column += ", paddress2";
                    values += ", ''";
                    column += ", pcity";
                    values += ", ''";
                    column += ", pstate";
                    values += ", ''";
                    column += ", pcountry";
                    values += ", ''";
                    column += ", ppin";
                    values += ", ''";
                    column += ", pphone1";
                    values += ", ''";
                    column += ", pphone2";
                    values += ", ''";
                    column += ", pfax";
                    values += ", ''";
                    column += ", pemail";
                    values += ", ''";
                    column += ", membership_start_date";
                    if (!memStartDate.equals("")) {
                        values += ", '" + memStartDate + "'";
                    }
                    column += ", membership_expiry_date";
                    if (!memEndDate.equals("")) {
                        values += ", '" + memEndDate + "'";
                    }
                    column += ", delinquency_reason";
                    values += ", ''";
                    column += ", comm_email";
                    values += ", 'A'";
                    column += ", comm_instant_msg";
                    values += ", 'A'";
                    column += ", comm_print";
                    values += ", 'A'";
                    column += ", entry_date";
                    values += ", current_date";
                    column += ", user_password";
                    values += ", 'abc'";
                    if (courseId != 0) {
                        column += ", course_id";
                        values += ", '" + courseId + "'";
                    }
                    column += ", status";
                    values += ", 'A'";
                    column += ", send_to_address";
                    values += ", 'A'";
                    column += ", custom";
                    values += ", ''";
                    column += ", privilege";
                    values += ", ''";
                    query = "INSERT INTO patron(" + column + ") VALUES (" + values + ")";
                }
                System.out.println("Query :::::::::::: " + query);
                try {
                    Statement stmt = conn.createStatement();
                    stmt.executeUpdate(query);
                    stmt.close();
                } catch (Exception exp) {
                    Object[] excRow = new Object[2];
                    excRow[0] = patronId;
                    excRow[1] = exp.getMessage();
                }
                conn.close();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    private void closeConnection() {
        try {
            if (con != null) {
                con.close();
            }
            con = null;
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public String customFields(String[] cusvals) {
        org.jdom.Element ele = new org.jdom.Element("Root");

        if (cusvals[5] != null && !cusvals[5].trim().equals("")) {
            org.jdom.Element ele5 = new org.jdom.Element("Field");
            ele5.setAttribute("NAME", "Invoice No");
            ele5.setAttribute("DISPLAYNAME", "Invoice No");
            ele5.setAttribute("TYPE", "SYSTEM");
            ele5.setAttribute("DISPLAY", "YES");
            ele5.setText(cusvals[5].trim());
            ele.addContent(ele5);
        }
        if (cusvals[1] != null && !cusvals[1].trim().equals("")) {
            org.jdom.Element ele2 = new org.jdom.Element("Field");
            ele2.setAttribute("NAME", "Vendor Name");
            ele2.setAttribute("DISPLAYNAME", "Vendor Name");
            ele2.setAttribute("TYPE", "SYSTEM");
            ele2.setAttribute("DISPLAY", "YES");
            ele2.setText(cusvals[1].trim());
            ele.addContent(ele2);
        }

        if (cusvals[2] != null && !cusvals[2].trim().equals("")) {
            org.jdom.Element ele3 = new org.jdom.Element("Field");
            ele3.setAttribute("NAME", "DateOfOreder");
            ele3.setAttribute("DISPLAYNAME", "DateOfOreder");
            ele3.setAttribute("TYPE", "SYSTEM");
            ele3.setAttribute("DISPLAY", "YES");
            ele3.setText(cusvals[2].trim());
            ele.addContent(ele3);
        }

        if (cusvals[3] != null && !cusvals[3].trim().equals("")) {
            org.jdom.Element ele3 = new org.jdom.Element("Field");
            ele3.setAttribute("NAME", "Discount");
            ele3.setAttribute("DISPLAYNAME", "Discount");
            ele3.setAttribute("TYPE", "SYSTEM");
            ele3.setAttribute("DISPLAY", "YES");
            ele3.setText(cusvals[3].trim());
            ele.addContent(ele3);
        }

        if (cusvals[4] != null && !cusvals[4].trim().equals("")) {
            org.jdom.Element ele5 = new org.jdom.Element("Field");
            ele5.setAttribute("NAME", "Actual Price");
            ele5.setAttribute("DISPLAYNAME", "Actual Price");
            ele5.setAttribute("TYPE", "SYSTEM");
            ele5.setAttribute("DISPLAY", "YES");
            ele5.setText(cusvals[4].trim());
            ele.addContent(ele5);
        }

        if (cusvals[6] != null && !cusvals[6].trim().equals("")) {
            org.jdom.Element ele5 = new org.jdom.Element("Field");
            ele5.setAttribute("NAME", "Foreign Price");
            ele5.setAttribute("DISPLAYNAME", "Foreign Price");
            ele5.setAttribute("TYPE", "SYSTEM");
            ele5.setAttribute("DISPLAY", "YES");
            ele5.setText(cusvals[6].trim());
            ele.addContent(ele5);
        }

        if (cusvals[0] != null && !cusvals[0].trim().equals("")) {
            org.jdom.Element ele4 = new org.jdom.Element("Field");
            ele4.setAttribute("NAME", "Invoice Date");
            ele4.setAttribute("DISPLAYNAME", "Invoice Date");
            ele4.setAttribute("TYPE", "SYSTEM");
            ele4.setAttribute("DISPLAY", "YES");
            ele4.setText(cusvals[0].trim());
            ele.addContent(ele4);
        }
        if (cusvals[7] != null && !cusvals[0].trim().equals("")) {
            org.jdom.Element ele6 = new org.jdom.Element("Field");
            ele6.setAttribute("NAME", "Department");
            ele6.setAttribute("DISPLAYNAME", "Department");
            ele6.setAttribute("TYPE", "SYSTEM");
            ele6.setAttribute("DISPLAY", "YES");
            ele6.setText(cusvals[7].trim());
            ele.addContent(ele6);
        }
        if (cusvals[8] != null && !cusvals[0].trim().equals("")) {
            org.jdom.Element ele7 = new org.jdom.Element("Field");
            ele7.setAttribute("NAME", "course");
            ele7.setAttribute("DISPLAYNAME", "course");
            ele7.setAttribute("TYPE", "SYSTEM");
            ele7.setAttribute("DISPLAY", "YES");
            ele7.setText(cusvals[8].trim());
            ele.addContent(ele7);
        }

        org.jdom.output.XMLOutputter xout = new org.jdom.output.XMLOutputter();
        xout.setOmitDeclaration(true);
        xout.setOmitEncoding(true);
        return xout.outputString(ele);
    }

    private void mapToMarcAndSave(String accessionno, String classno, String location, String author, String publisher, String language, String year, String edition, String phydscr, String priceinrs, String bookno, String corpname, String isbn, String locaton, String dentry, String title, String bibnote, String place, String subject, String author1) {
        CatalogMaterialDescription cmd = new CatalogMaterialDescription();

//        System.out.println("*****************record no*********************" + recno);

        String[] cu = {"", "", "", "", priceinrs, "", "", "", ""};
        String cust = customFields(cu);
//
//        Field fld1 = new Field("905", '0', '0');
//        SubField sf1 = new SubField('a', date);
//        fld1.appendSubField(sf1);
//        cmd.addField(fld1);
//
        Field fld2 = new Field("901", '0', '0');
        SubField sf2 = null;
        if (accessionno != null && !accessionno.trim().equals("")) {
            sf2 = new SubField('a', accessionno);
            fld2.appendSubField(sf2);
            sf2 = new SubField('c', accessionno);
            fld2.appendSubField(sf2);
        }
        if (location != null && !location.trim().equals("")) {
            if (location == null ? "REF" == null : location.substring(0, 3).toUpperCase().equals("REF")) {
                sf2 = new SubField('m', "63");
            } else {
                sf2 = new SubField('m', "8");
            }
            fld2.appendSubField(sf2);
        }
        if (cust != null && !cust.trim().equals("")) {
            sf2 = new SubField('l', cust);
            fld2.appendSubField(sf2);
        }
        if (locaton != null && !locaton.trim().equals("")) {
            sf2 = new SubField('d', locaton);
            fld2.appendSubField(sf2);
        }
        if (bookno != null && bookno.trim().equals("")) {
            sf2 = new SubField('e', bookno);
            fld2.appendSubField(sf2);
        }
        if (classno != null && !classno.trim().equals("")) {
            sf2 = new SubField('f', classno);
            fld2.appendSubField(sf2);
            if (bookno != null && bookno.trim().equals("")) {
                sf2 = new SubField('g', classno + bookno);
                fld2.appendSubField(sf2);
            }
        }
        cmd.addField(fld2);

        if (corpname != null && !corpname.trim().equals("")) {
            Field fld24 = new Field("110", '0', '0');
            SubField sf24 = new SubField('a', corpname);
            fld24.appendSubField(sf24);
            cmd.addField(fld24);
        }
        if (isbn != null && !isbn.trim().equals("")) {
            Field fld25 = new Field("020", '0', '0');
            SubField sf25 = new SubField('a', isbn);
            fld25.appendSubField(sf25);
            cmd.addField(fld25);
        }

        if (dentry != null && !dentry.trim().equals("")) {
            Field fld24 = new Field("903", '0', '0');
            SubField sf24 = new SubField('a', dentry);
            fld24.appendSubField(sf24);
            cmd.addField(fld24);
        }
        if (bibnote != null && !bibnote.trim().equals("")) {
            Field fld25 = new Field("500", '0', '0');
            SubField sf25 = new SubField('a', bibnote);
            fld25.appendSubField(sf25);
            cmd.addField(fld25);
        }
        if (author1 != null && !author1.trim().equals("")) {
            Field fld26 = new Field("700", '0', '0');
            SubField sf26 = new SubField('a', author1);
            fld26.appendSubField(sf26);
            cmd.addField(fld26);
        }
        if (subject != null && !subject.trim().equals("")) {
            Field fld27 = new Field("650", '0', '0');
            SubField sf27 = new SubField('a', subject);
            fld27.appendSubField(sf27);
            cmd.addField(fld27);
        }
//
        if (author != null && !author.trim().equals("")) {
            Field fld3 = new Field("100", '0', '0');
            SubField sf3 = new SubField('a', author);
            fld3.appendSubField(sf3);
            cmd.addField(fld3);
        }

//        Field fld4 = new Field("245", '0', '0');
//        SubField sf4 = new SubField('a', title + " Vol:" + volume);
//        fld4.appendSubField(sf4);
//        cmd.addField(fld4);
        if (title != null && !title.trim().equals("")) {
            Field fld4 = new Field("245", '0', '0');
            SubField sf4 = new SubField('a', title);
            fld4.appendSubField(sf4);
            cmd.addField(fld4);
        }
//
        if (edition != null && !edition.trim().equals("")) {
            Field fld5 = new Field("250", '0', '0');
            SubField sf5 = new SubField('a', edition);
            fld5.appendSubField(sf5);
            cmd.addField(fld5);
        }

        Field fld10 = new Field("260", '0', '0');
        SubField sf10 = null;
        if (place != null && !place.trim().equals("")) {
            sf10 = new SubField('a', place);
            fld10.appendSubField(sf10);
        }
        if (publisher != null && !publisher.trim().equals("")) {
            sf10 = new SubField('b', publisher);
            fld10.appendSubField(sf10);
        }
        if (year != null && !year.trim().equals("")) {
            sf10 = new SubField('c', year);
            fld10.appendSubField(sf10);
        }
        cmd.addField(fld10);
//
//        Field fld11 = new Field("300", '0', '0');
//        SubField sf11 = new SubField('a', pages + " " + sizeinch + " " + bind);
//        fld11.appendSubField(sf11);
//        cmd.addField(fld11);
        if (phydscr != null && !phydscr.trim().equals("")) {
            Field fld11 = new Field("300", '0', '0');
            SubField sf11 = new SubField('a', phydscr);
            fld11.appendSubField(sf11);
            cmd.addField(fld11);
        }

        if (language != null && !language.trim().equals("")) {
            Field fld14 = new Field("906", '0', '0');
            SubField sf14 = new SubField('a', language);
            fld14.appendSubField(sf14);
            cmd.addField(fld14);
        }

////        Field fld18 = new Field("650", '0', '0');
////        SubField sf18 = new SubField('a', dept);
////        fld18.appendSubField(sf18);
////        cmd.addField(fld18);
////
////        Field fld19 = new Field("500", '0', '0');
////        SubField sf19 = new SubField('a', remarks);
////        fld19.appendSubField(sf19);
////        cmd.addField(fld19);
//
        newgenlib.marccomponent.marcmodel.FixedField fld27 = new newgenlib.marccomponent.marcmodel.FixedField();
        cmd.setFixedField(fld27);

        newgenlib.marccomponent.marcmodel.ControlField cfl = new newgenlib.marccomponent.marcmodel.ControlField();
        cfl.setTag("007");
        cfl.setData("");
        java.util.ArrayList cflArlst = new java.util.ArrayList();
        cflArlst.add(cfl);
        cmd.addControlField(cflArlst);

        org.marc4j.marc.impl.LeaderImpl leaderimpl = new org.marc4j.marc.impl.LeaderImpl();
        leaderimpl.setRecordStatus('n');
        leaderimpl.setTypeOfRecord('a');
        char ch[] = new char[2];

        ch[0] = 'm';

        // System.out.println("Bibliographic level is: "+ch[0]);
        ch[1] = '#';
        leaderimpl.setImplDefined1(ch);
        char ch1[] = new char[3];
        ch1[0] = 'u';
        ch1[1] = 'a';
        ch1[2] = '#';
        leaderimpl.setImplDefined2(ch1);
        char ch2[] = new char[4];
        ch2[0] = '1';
        ch2[1] = '2';
        ch2[2] = '3';
        ch2[2] = '4';
        leaderimpl.setEntryMap(ch2);
        leaderimpl.setCharCodingScheme('a');
        org.marc4j.marc.Leader leader = leaderimpl;

        // String  nmk1=String.valueOf((new java.util.Date()).getTime());
        //printMarcToFile(cmdMARC,"1182");
        //System.out.println("leader is............."+leader.marshal());
        cmd.getFixedField().setLeader(leader.marshal());
//
        pmarc.persist(cmd, "1", "3", "");
//
//        int recordcount = 0;
//        if (recordcount % 100 == 0) {
//            System.out.println(recordcount);
//        }
        printCMD(cmd);
//        recno++;

    }
////
//

    public void printCMD(CatalogMaterialDescription cmd) {
        Field[] fields = cmd.getFields();
        //System.out.println("At print cmd");
        for (int i = 0; i < fields.length; i++) {
            Field f = fields[i];
            System.out.println(f.getTag());
            //        pw.println(f.getTag());
            SubField[] ss = f.getSubFields();
            if (ss != null) {
                for (int j = 0; j
                        < ss.length; j++) {
                    SubField s = ss[j];
                    System.out.println("\t" + s.getIdentifier() + "\t\t" + s.getData());
                    //              pw.println("\t"+s.getIdentifier()+"\t\t"+s.getData());
                }

            }
        }
        System.out.println("###################################################################" + in++);
    }

    private void dataEntered(String mem_cd, String acc_no, String iss_dt, String user_cd, String due_dt) {
        try {
//            int flag = 1;
            Class.forName("org.postgresql.Driver");
            Connection conn = null;
            try {
                System.out.println("jdbc:postgresql://" + hostName.tfHost.getText() + ":" + hostName.tfPort.getText() + "/" + hostName.tfDatabaseName.getText() + "," + hostName.tfUserName.getText() + "," + String.valueOf(hostName.tfpassword.getPassword()));
                conn = DriverManager.getConnection("jdbc:postgresql://" + hostName.tfHost.getText() + ":" + hostName.tfPort.getText() + "/" + hostName.tfDatabaseName.getText(), hostName.tfUserName.getText(), String.valueOf(hostName.tfpassword.getPassword()));
            } catch (Exception e) {
                e.printStackTrace();
//                javax.swing.JOptionPane.showMessageDialog(null, "postgresql server connection refused", "error", JOptionPane.ERROR_MESSAGE);
//                System.exit(0);
            }
            if (conn != null) {
                Statement st1 = conn.createStatement();
                Statement stmt1 = conn.createStatement();
                System.out.println("select accession_number from document where accession_number='" + acc_no.trim() + "'");
                ResultSet rs2 = st1.executeQuery("select accession_number from document where accession_number='" + acc_no.trim() + "'");
                String docac = "";
                while (rs2.next()) {
                    docac = rs2.getString(1);
                    System.out.println("^^^^^^^^^^^^^^" + docac);
                }
                if (docac.trim().equals(acc_no.trim())) {

                    System.out.println("********accession number exists" + acc_no + " and" + docac);
                } else {

                    System.out.println("INSERT INTO document(accession_number, library_id)VALUES ('" + acc_no.trim() + "',1);");
                    stmt1.executeUpdate("INSERT INTO document(accession_number, library_id)VALUES ('" + acc_no.trim() + "',1);");
                    System.out.println(ti++ + "************************" + acc_no);
                }

                Statement stm1 = conn.createStatement();
                ResultSet rs1 = stm1.executeQuery("select max(ta_id) from cir_transaction");
                while (rs1.next()) {
                    maxal = rs1.getInt(1);
                }
                maxval = maxal + 1;
                rs1.close();
                Statement stmt2 = conn.createStatement();
                System.out.println("INSERT INTO cir_transaction(ta_id, library_id, patron_id, ta_date,accession_number, document_library_id, due_date, checkout_id,status) VALUES (" + maxval + ", 1, '" + mem_cd + "', '" + iss_dt + "','" + acc_no.trim() + "', 1, '" + due_dt + "', '" + user_cd + "','A');");
                stmt2.executeUpdate("INSERT INTO cir_transaction(ta_id, library_id, patron_id, ta_date,accession_number, document_library_id, due_date, checkout_id,status) VALUES (" + maxval + ", 1, '" + mem_cd + "', '" + iss_dt + "','" + acc_no.trim() + "', 1, '" + due_dt + "', '" + user_cd + "','A');");

                Statement stm2 = conn.createStatement();
                stm2.executeUpdate("update document set status='A'where accession_number='" + acc_no.trim() + "'");
                System.out.println("update document set status='A'where accession_number='" + acc_no.trim() + "'");

                stm2.close();
                stmt2.close();
                stm1.close();
//                st2.close();
                stmt1.close();
                rs2.close();
                st1.close();
                conn.close();
                }
//            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }

    }
}
